package AccessDB;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import model.Materia;
import model.Serie;

public class SerieDAO {
	
	private Connection con;
	
	public SerieDAO(){
		super();
		con =  ConnectionDB.getInstance().getConnection();
	}
	
	public Serie retornaSerie(int idSerie) throws SQLException{
		
		PreparedStatement stmt = con.prepareStatement("select * from Serie where idSerie = ?");
		stmt.setInt(1, idSerie);
		ResultSet rs = stmt.executeQuery();
		rs.next();
		return gerarSerie(rs);
		
	}
	
	public void adicionarSerie(String tituloSerie, int[] idsMaterias) throws SQLException
	{
		PreparedStatement stmt = con.prepareStatement("insert into Serie values (?)");
		stmt.setString(1, tituloSerie);
		stmt.execute();
		
		PreparedStatement stmt2 = con.prepareStatement("insert into SerieMateria values (?,?)");
		stmt2.setInt(2, this.retornaUltima().getIdSerie());
		
		for(int i = 0; i < idsMaterias.length; i++)
		{
			stmt2.setInt(1, idsMaterias[i]);
			stmt2.execute();
		}
	}
	
	public void adicionarMateriaNaSerie(int idSerie, int idMateria) throws SQLException
	{
		PreparedStatement stmt = con.prepareStatement("insert into SerieMateria values (?,?)");
		stmt.setInt(1, idMateria);
		stmt.setInt(2, idSerie);
		
		stmt.execute();
	}
	
	public void excluirMateriaNaSerie(int idSerie, int idMateria) throws SQLException
	{
		PreparedStatement stmt = con.prepareStatement("delete SerieMateria where idSerie = ? and idMateria = ?");
		stmt.setInt(1, idSerie);
		stmt.setInt(2, idMateria);
		
		stmt.execute();
	}	
	
	public void editarTitulo(String novoTitulo, int idSerie) throws SQLException
	{
		PreparedStatement stmt = con.prepareStatement("update Serie set titulo = ? where idSerie = ?");
		stmt.setString(1, novoTitulo);
		stmt.setInt(2, idSerie);
		
		stmt.executeUpdate();
	}
	
	public void excluirMateria(int idSerie) throws SQLException
	{
		PreparedStatement stmt = con.prepareStatement("delete Serie where idSerie = ?");
		stmt.setInt(1, idSerie);
		
		stmt.execute();
	}

	
	public Serie retornaUltima() throws SQLException
	{
		PreparedStatement stmt = con.prepareStatement("select * from Serie where idSerie = (select MAX(idSerie) from Serie)");
		ResultSet rs = stmt.executeQuery();
		rs.next();
		return this.gerarSerie(rs);
	}
	
	public List<Serie> retornaTodas() throws SQLException
	{
		PreparedStatement stmt = con.prepareStatement("select * from Serie");
		ResultSet rs = stmt.executeQuery();
		
		List<Serie> series = new ArrayList<Serie>();
		
		while(rs.next())
			series.add(this.gerarSerie(rs));
		
		return series;
	}
	
	
	
	public Serie gerarSerie(ResultSet rs) throws SQLException{
		
		Serie serie = new Serie();

		serie.setIdSerie(rs.getInt("idSerie"));
		serie.setMaterias(new MateriaDAO().retornaMateriasSerie(serie.getIdSerie()));
		serie.setTitulo(rs.getString("titulo"));
		return serie;
		
	}
	
}